<?php

declare(strict_types=1);

namespace App\Order\Service;

use App\Common\Service\BaseService;
use App\Common\Constants\Stakeholder;
use App\Order\Model\OrderGoodsModel;
use App\Order\Repository\RepositoryFactory;
use App\Resource\Service\CategoryService;
use Hyperf\Database\Concerns\BuildsQueries;
use Hyperf\Database\Query\Builder;
use Hyperf\Di\Annotation\Inject;
use Hyperf\Logger\LoggerFactory;
use Hyperf\Utils\ApplicationContext;

class OrderGoodsService extends BaseService
{
    /**
     * @Inject()
     * @var CategoryService
     */
    private $cate;


    protected $orderGoodsRepository;

    public function __construct(LoggerFactory $loggerFactory)
    {
        parent::__construct();
        $container = ApplicationContext::getContainer();
        $this->orderGoodsRepository = $container->get(RepositoryFactory::class)->getRepository("orderGoods");

    }

    /**
     * 订单商品列表
     * @param array $where
     * @param array|string[] $field
     * @param array|null $in
     * @return array
     */
    public function getOrderGoodsList(array $where = [], array $field = ['*'], ?array $in = null)
    {
        return OrderGoodsModel::query()
            ->where($where)
            ->when($in, function ($query, $in) {
                return $query->whereIn($in[0], $in[1]);
            })
            ->get($field)
            ->toArray();
    }

    /**
     * 订单商品名字 字符串连接
     * @param array $order_no
     * @return array
     */
    public function orderGoodsName(array $order_no = [])
    {
        return OrderGoodsModel::query()
            ->selectRaw('GROUP_CONCAT(goods_title) as goods_title,order_no')
            ->whereIn('order_no', $order_no)
            ->groupBy(['order_no'])
            ->get()
            ->toArray();
    }

    /**
     * @param array $params
     * @return array
     */
    public function salePty(array $params)
    {
        $paidQty = OrderGoodsModel::query()
            ->selectRaw('SUM(og.number) as sale_qty')
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['order.status', '<>', Stakeholder::ORDER_REFUNDED]
            ])
            ->whereBetween('og.create_at', $params['date'])
            ->first()
            ->toArray();
        $contrastQty = OrderGoodsModel::query()
            ->selectRaw('SUM(og.number) as sale_qty')
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['order.status', '<>', Stakeholder::ORDER_REFUNDED]
            ])
            ->whereBetween('og.create_at', $params['contrasttime'])
            ->first()
            ->toArray();

        $trend = $this->coefficientsAndTrends((string)$paidQty['sale_qty'], (string)$contrastQty['sale_qty']);

        return ['amt' => $paidQty['sale_qty'] ?? 0, 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];
    }

    /**
     * @param array $params
     * @return array
     */
    public function dateGroupSaleQty(array $params)
    {
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(og.create_at, '%Y-%m-%d') as date,SUM(og.number) as sale_qty";
        } else {
            $selectRaw = "DATE_FORMAT(og.create_at, '%Y-%m') as date,SUM(og.number) as sale_qty";
        }
        $paidSaleQty = OrderGoodsModel::query()
            ->selectRaw($selectRaw)
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('og.create_at', $params['date'])
            ->groupBy(['date'])
            ->get()
            ->toArray();
        $contrastSaleQty = OrderGoodsModel::query()
            ->selectRaw($selectRaw)
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('og.create_at', $params['contrasttime'])
            ->groupBy(['date'])
            ->get()
            ->toArray();
        return ['basic' => $paidSaleQty, 'contrast' => $contrastSaleQty];
    }


    /**
     * 财务报表--商品数据分析（含龙收银分类）
     * @param array $params
     * @return BuildsQueries|\Hyperf\Database\Model\Builder|Builder|mixed
     * @author liule
     */
    public function goodsDataAnalysis(array $params)
    {
        if (!empty($params['sort_field']) && !empty($params['sort_key'])) {
            $sort = ['sort_field' => trim($params['sort_field']), 'sort_key' => trim($params['sort_key'])];
        }
        $select = [
            'SUM(og.number) as sales_volume',
            'SUM(og.deductMoney) as goods_discount_amount',
//            'SUM(og.dis_price) as goods_amt',
            'og.shop_id',
            'og.goods_id',
            'og.goods_title',
            'og.lsy_goods_name',
            'og.lsy_unit_name',
            'og.lsy_class_name',
            'cate.title as cate_name',
            'crdCate.title as crd_cate_name',
            '(SUM(og.dis_price) + SUM(og.deductMoney) - IFNULL(memb_ref_amt,0) - IFNULL(shop_ref_amt,0)) as receivable_amt',
            '(SUM(og.dis_price) - IFNULL(memb_ref_amt,0) - IFNULL(shop_ref_amt,0)) as actually_paid'
        ];
        $joinSubRefund = $this->joinSubRefundInfo($params);
        return OrderGoodsModel::query()
            ->selectRaw(implode(',', $select))
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->leftJoin('store_goods_cate as cate', 'og.cate_id', '=', 'cate.id')
            ->leftJoin('store_crd_goods_cate as crdCate', 'og.crd_cate_id', '=', 'crdCate.id')
            ->leftJoinSub($joinSubRefund, 'refund', function ($join) {
                $join->on('og.goods_id', '=', 'refund.goods_id');
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when($params['goods_title'] ?? 0, function ($query, $goods_title) {
                return $query->whereRaw('INSTR(og.goods_title, ?) > 0', [$goods_title])
                    ->orWhereRaw('INSTR(og.lsy_goods_name, ?) > 0', [$goods_title]);
            })
            ->when(is_numeric($params['cate_id']), function ($query) use ($params) {
                return $query->where('og.cate_id', $params['cate_id']);
            })
            ->when(is_numeric($params['crd_cate_id']), function ($query) use ($params) {
                return $query->where('og.crd_cate_id', $params['crd_cate_id']);
            })
            ->when($params['lsy_class_name'] ?? 0, function ($query, $lsy_class_name) {
                return $query->where('og.lsy_class_name', $lsy_class_name);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->groupBy(['og.goods_id'])
            ->when($sort ?? 0, function ($query, $sort) {
                return $query->orderBy($sort['sort_field'], $sort['sort_key']);
            })
            ->when($params['perpage'] ?? 0, function ($query, $perPage) {
                return $query->paginate((int)$perPage);
            }, function ($query) {
                return $query->get();
            });
    }

    /**
     * 财务报表--商品数据分析退款子查询
     * @param array $params
     * @return BuildsQueries|\Hyperf\Database\Model\Builder|Builder|mixed
     * @author liule
     */
    public function joinSubRefundInfo(array $params)
    {
        $select = [
            'SUM(og.refund_number * og.goods_dis_price) as memb_ref_amt',
            'SUM(og.shop_refund_number * og.goods_dis_price) as shop_ref_amt',
            'og.goods_id'
        ];
        return OrderGoodsModel::query()
            ->selectRaw(implode(',', $select))
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->leftJoin('store_goods_cate as cate', 'og.cate_id', '=', 'cate.id')
            ->leftJoin('store_crd_goods_cate as crdCate', 'og.crd_cate_id', '=', 'crdCate.id')
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when($params['goods_title'] ?? 0, function ($query, $goods_title) {
                return $query->whereRaw('INSTR(og.goods_title, ?) > 0', [$goods_title])
                    ->orWhereRaw('INSTR(og.lsy_goods_name, ?) > 0', [$goods_title]);
            })
            ->when(is_numeric($params['cate_id']), function ($query) use ($params) {
                return $query->where('og.cate_id', $params['cate_id']);
            })
            ->when(is_numeric($params['crd_cate_id']), function ($query) use ($params) {
                return $query->where('og.crd_cate_id', $params['crd_cate_id']);
            })
            ->when($params['lsy_class_name'] ?? 0, function ($query, $lsy_class_name) {
                return $query->where('og.lsy_class_name', $lsy_class_name);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['og.status', '=', 1],
            ])
            ->groupBy(['goods_id']);
    }


    /**
     * 商品销售分析
     * @param array $params
     * @return BuildsQueries|\Hyperf\Database\Model\Builder|Builder|mixed
     */
    public function goodsSaleAnalysisList(array $params)
    {
        if (!empty($params['sort_field']) && !empty($params['sort_key'])) {
            $sort = ['sort_field' => $params['sort_field'], 'sort_key' => $params['sort_key']];
        }
        $select = [
            'SUM(og.number) as sales_volume',
            'SUM(og.deductMoney) as deductMoney',
            'SUM(og.dis_price) as goods_amt',
            'og.goods_id',
            'og.lsy_class_name',
            'og.goods_title',
            'og.goods_spec',
            'og.selling_price',
            'cate.title as cate_name',
            'crdCate.title as crd_cate_name',
            'order.order_source',
            'memb_ref_num',
            'shop_ref_num',
            'memb_ref_amt',
            'shop_ref_amt'
        ];
        $joinSubRefund = $this->joinSubRefund($params);

        return OrderGoodsModel::query()
            ->selectRaw(implode(',', $select))
            ->from('store_order_goods as og')
            ->leftJoin('store_goods_cate as cate', 'og.cate_id', '=', 'cate.id')
            ->leftJoin('store_crd_goods_cate as crdCate', 'og.crd_cate_id', '=', 'crdCate.id')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->leftJoinSub($joinSubRefund, 'refund', function ($join) {
                $join->on('og.goods_id', '=', 'refund.goods_id');
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when($params['goods_title'] ?? 0, function ($query, $goods_title) {
                return $query->whereRaw('INSTR(og.goods_title, ?) > 0', [$goods_title]);
            })
            ->when($params['goods_id'] ?? 0, function ($query, $goods_id) {
                return $query->where('og.goods_id', $goods_id);
            })
            ->when($params['cate_id'] ?? 0, function ($query, $cate_id) {
                return $query->where('og.cate_id', $cate_id);
            })
            ->when($params['crd_cate_id'] ?? 0, function ($query, $crd_cate_id) {
                return $query->where('og.crd_cate_id', $crd_cate_id);
            })
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('order.pay_at', $params['date'])
            ->groupBy(['og.goods_id'])
            ->when($sort ?? 0, function ($query, $sort) {
                return $query->orderBy($sort['sort_field'], $sort['sort_key']);
            })
            ->when($params['perpage'] ?? 0, function ($query, $perPage) {
                return $query->paginate((int)$perPage);
            }, function ($query) {
                return $query->get();
            });
    }

    /**
     * 商品退款子查询
     * @param array $params
     * @return BuildsQueries|\Hyperf\Database\Model\Builder|Builder|mixed
     */
    public function joinSubRefund(array $params)
    {
        $select = [
            'SUM(og.refund_number) as memb_ref_num',
            'SUM(og.shop_refund_number) as shop_ref_num',
            'SUM(og.refund_number * og.goods_dis_price) as memb_ref_amt',
            'SUM(og.shop_refund_number * og.goods_dis_price) as shop_ref_amt',
            'og.goods_id'
        ];
        return OrderGoodsModel::query()
            ->selectRaw(implode(',', $select))
            ->from('store_order_goods as og')
            ->leftJoin('store_goods_cate as cate', 'og.cate_id', '=', 'cate.id')
            ->leftJoin('store_crd_goods_cate as crdCate', 'og.crd_cate_id', '=', 'crdCate.id')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->when($params['goods_title'] ?? 0, function ($query, $goods_title) {
                return $query->whereRaw('INSTR(og.goods_title, ?) > 0', [$goods_title]);
            })
            ->when($params['goods_id'] ?? 0, function ($query, $goods_id) {
                return $query->where('og.goods_id', $goods_id);
            })
            ->when($params['cate_id'] ?? 0, function ($query, $cate_id) {
                return $query->where('og.cate_id', $cate_id);
            })
            ->when($params['crd_cate_id'] ?? 0, function ($query, $crd_cate_id) {
                return $query->where('og.crd_cate_id', $crd_cate_id);
            })
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['og.status', '=', 1],
            ])
            ->whereBetween('og.refund_at', $params['date'])
            ->groupBy(['goods_id']);
    }

    /**
     * 分类销售明细
     * @param array $params
     * @return array
     */
    public function cateSalesDetails(array $params)
    {
        if ($params['flag'] == 1) {
            $paidDate = $this->prDates($params['date'][0], $params['date'][1]);
            $selectRaw = "DATE_FORMAT(og.create_at, '%Y-%m-%d') as date,SUM(og.number) as total_qty, SUM(og.goods_dis_price * og.number) as total_amount,SUM(og.deductMoney) as deductMoney";
        } else {
            $paidDate = $this->prMonths($params['date'][0], $params['date'][1]);
            $selectRaw = "DATE_FORMAT(og.create_at, '%Y-%m') as date,SUM(og.number) as total_qty, SUM(og.goods_dis_price * og.number) as total_amount,SUM(og.deductMoney) as deductMoney";
        }
        $selectRawBasic = "SUM(og.number) as total_qty, SUM(og.goods_dis_price * og.number) as total_amount,SUM(og.deductMoney) as deductMoney";
        if ($params['order_source'] == 1){
            $crdCateName = $this->cate->getList(['shop_type' => 1],500,['id', 'title'])['data']->toArray();
            $cateName = array_column($crdCateName, 'title', 'id');
            $selectRaw = $selectRaw . ',og.crd_cate_id as cate_id';
            $selectRawBasic = $selectRawBasic . ',og.crd_cate_id as cate_id';
        }else{
            $cateName = $this->cate->getCateNameToIdKey();
            $selectRaw = $selectRaw . ',og.cate_id';
            $selectRawBasic = $selectRawBasic . ',og.cate_id';
        }

        $list = OrderGoodsModel::query()
            ->selectRaw($selectRawBasic)
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            },function ($query){
                return $query->where('order.order_source', Stakeholder::TIMELY_ORDER);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['order.status', '<>', Stakeholder::ORDER_REFUNDED]
            ])
            ->whereBetween('og.create_at', $params['date'])
            ->groupBy(['cate_id'])
            ->get()
            ->toArray();
        $dateList = OrderGoodsModel::query()
            ->selectRaw($selectRaw)
            ->from('store_order_goods as og')
            ->leftJoin('store_order as order', 'og.order_no', '=', 'order.order_no')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order.order_source', $params['order_source']);
            },function ($query){
                return $query->where('order.order_source', Stakeholder::TIMELY_ORDER);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order.order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('og.shop_id', $shop_id);
            })
            ->where([
                ['order.is_pay', '=', Stakeholder::ORDER_PAID],
                ['order.status', '<>', Stakeholder::ORDER_REFUNDED]
            ])
            ->whereBetween('og.create_at', $params['date'])
            ->groupBy(['cate_id', 'date'])
            ->get()
            ->toArray();

        $dl = [];
        foreach (array_column($list, 'cate_id') as $k => $v) {
            foreach ($paidDate as $kk => $vv) {
                if ($params['flag'] == 1) {
                    $dl[$v][$kk]['date'] = substr($vv, -5);
                } else {
                    $dl[$v][$kk]['date'] = $vv;
                }
                $dl[$v][$kk]['total_qty'] = 0;
                $dl[$v][$kk]['total_amount'] = 0;
                $dl[$v][$kk]['deductMoney'] = 0;
                $dl[$v][$kk]['cate_id'] = $v;
                $dl[$v][$kk]['cate_name'] = $cateName[$v];
                foreach ($dateList as $kkk => $vvv) {
                    if ($v == $vvv['cate_id'] && $vv == $vvv['date']) {
                        $dl[$v][$kk]['total_qty'] = $vvv['total_qty'];
                        $dl[$v][$kk]['total_amount'] = bcsub((string)$vvv['total_amount'], (string)$vvv['deductMoney'], 2);
                        $dl[$v][$kk]['deductMoney'] = $vvv['deductMoney'];
                    }
                }
            }
        }
        $totalAmt = bcsub((string)array_sum(array_column($list, 'total_amount')), (string)array_sum(array_column($list, 'deductMoney')), 2);

        foreach ($list as $k => $v) {
            $list[$k]['total_amount'] = bcsub((string)$v['total_amount'], (string)$v['deductMoney'], 2);
            $list[$k]['cate_name'] = $cateName[$v['cate_id']];
            $list[$k]['avg_amt'] = bcmul((string)round(bcdiv((string)$v['total_amount'], (string)$totalAmt, 6), 4), '100', 2) . '%';
            $list[$k]['date_list'] = $dl[$v['cate_id']];
        }
        return $list;
    }

    /**
     * 运营分析---拼团活动数据分析
     * @param array $activityGoodsId
     * @return array
     */
    public function getGoodsInfoByActivityGoodsId(array $activityGoodsId)
    {
        return OrderGoodsModel::query()
            ->selectRaw('SUM(selling_price * number) as amt,SUM(number) as number,COUNT(distinct mid) as groupPerNum,activity_goods_id')
            ->whereIn('activity_goods_id', $activityGoodsId)
            ->groupBy(['activity_goods_id'])
            ->get()
            ->toArray();
    }

    /**
     * 数据更新
     * @param array $where
     * @param array $data
     * @return int
     */
    public function update(array $where, array $data)
    {
        if (in_array('in', $where)) {
            return OrderGoodsModel::query()->whereIn($where[0], $where[2])->update($data);
        }
        return OrderGoodsModel::query()->where($where)->update($data);
    }

}
